package com.yihuier.yihuier.Mapper.MeetingManager;

import com.yihuier.yihuier.Entity.MeetingManager.MeetingRoom;
import org.apache.ibatis.annotations.*;

import java.sql.Timestamp;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Mapper
public interface MeetingRoomMapper {

    @Select("SELECT meeting_id FROM `bz_meeting_room` WHERE room_id = #{room_id} AND meeting_time_start BETWEEN #{before} AND #{after};")
    int [] selectMeetingsByTimeAndRoom(@Param("before") Date before, @Param("after") Date after,@Param("room_id") int room_id);

    @Select("SELECT meeting_id FROM `bz_meeting_room` WHERE meeting_time_start BETWEEN #{before} AND #{after};")
    int [] selectMeetingsByTime(@Param("before") Date before, @Param("after") Date after);

    @Select("SELECT count(meeting_id) FROM `bz_meeting_room` WHERE room_id = #{room_id} AND meeting_time_start BETWEEN #{before} AND #{after};")
    int selectNumMeetingsByRoom(@Param("before") Date before, @Param("after") Date after,@Param("room_id") int room_id);

    @Select("SELECT count(meeting_id) FROM `bz_meeting_room` WHERE meeting_time_start BETWEEN #{before} AND #{after};")
    int selectNumMeetings(@Param("before") Date before, @Param("after") Date after);

    @Select("SELECT a.department_id,count(a.department_id) meeting_num FROM bz_department_user a RIGHT JOIN bz_meeting b ON a.user_id = b.applicant WHERE b.meeting_id in (SELECT meeting_id FROM `bz_meeting_room` WHERE room_id = #{room_id} AND meeting_time_start BETWEEN #{before} AND #{after}) GROUP BY department_id ORDER BY meeting_num DESC LIMIT 0,4;")
    List<Map<String,Object>> selectTopDepartmentByTimeAndRoom(@Param("before") Date before, @Param("after") Date after, @Param("room_id") int room_id);

    @Select("SELECT meeting_time_start,meeting_time FROM `bz_meeting_room` WHERE room_id = #{room_id} AND meeting_id = #{meeting_id}")
    Map<String,Object> selectMeetingTimes(@Param("room_id") Long room_id, @Param("meeting_id") Long meeting_id);

    @Results({
            @Result(property = "room_id",column = "room_id"),
            @Result(property = "meeting_id",column = "meeting_id"),
            @Result(property = "meeting_time_start",column = "meeting_time_start"),
            @Result(property = "meeting_time",column = "meeting_time"),
            @Result(property = "meeting_time_end",column = "meeting_time_end"),
    })
    @Select("SELECT room_id,meeting_time_start,meeting_time,meeting_time_end FROM bz_meeting_room WHERE room_id = #{room_id} AND TO_DAYS(meeting_time_start) = TO_DAYS(#{today}) ORDER BY meeting_time_start;")
    List<MeetingRoom> selectMeetingRoomInfoBetweenNowAndThenByRoomId(@Param("room_id")int room_id,@Param("today")Timestamp today);

    @Results({
            @Result(property = "room_id",column = "room_id"),
            @Result(property = "meeting_id",column = "meeting_id"),
            @Result(property = "meeting_time_start",column = "meeting_time_start"),
            @Result(property = "meeting_time",column = "meeting_time"),
            @Result(property = "meeting_time_end",column = "meeting_time_end"),
    })
    @Select("SELECT room_id,meeting_time_start,meeting_time,meeting_time_end FROM bz_meeting_room WHERE TO_DAYS(meeting_time_start) = TO_DAYS(#{today}) ORDER BY meeting_time_start;")
    List<MeetingRoom> selectMeetingRoomInfoBetweenNowAndThenWithOutRoomId(@Param("today")Timestamp today);

    @Results({
            @Result(property = "room_id",column = "room_id"),
            @Result(property = "meeting_id",column = "meeting_id"),
            @Result(property = "meeting_time_start",column = "meeting_time_start"),
            @Result(property = "meeting_time",column = "meeting_time"),
            @Result(property = "meeting_time_end",column = "meeting_time_end"),
    })
    @Select("SELECT room_id,meeting_time_start,meeting_time,meeting_time_end FROM bz_meeting_room WHERE room_id = #{room_id} AND TO_DAYS(meeting_time_start) IN (TO_DAYS(#{now}));")
    List<MeetingRoom> selectMeetingRoomToday(@Param("room_id") int room_id,@Param("now")Timestamp now);

    @Insert("INSERT INTO bz_meeting_room(room_id,meeting_id,meeting_time_start,meeting_time,meeting_time_end) VALUES(#{room_id},#{meeting_id},#{meeting_time_start},#{meeting_time},#{meeting_time_end});")
    int insetOneMeeting(@Param("room_id") int room_id,@Param("meeting_id")int meeting_id,@Param("meeting_time_start")Date start_time,@Param("meeting_time")int time,@Param("meeting_time_end")Date end_time);


    @Select("SELECT count(meeting_id) FROM `bz_meeting_room` WHERE room_id = #{room_id} AND (meeting_time_start BETWEEN #{before} AND #{after} OR meeting_time_end BETWEEN #{before} AND #{after} );")
    int selectThisTimeRoomIfUsing(@Param("room_id")int room_id,@Param("before") Date before, @Param("after") Date after);

    @Select("<script>" +
            " SELECT meeting_id" +
            " FROM" +
            " bz_meeting_room" +
            " WHERE" +
            " room_id IN" +
            " <foreach item='item' index='index' collection='room_ids' open='(' separator=',' close=')'>" +
            " #{item}" +
            " </foreach>" +
            " </script> ")
    int[] selectMeetingIds(@Param("room_ids") int[] room_ids);

@Select("SELECT meeting_time_start FROM bz_meeting_room WHERE meeting_id = #{meeting_id};")
    Date selectMeetingTimeByMeetingID(@Param("meeting_id")int meeting_id);
    @Select("SELECT meeting_time FROM bz_meeting_room WHERE meeting_id = #{meeting_id};")
    int selectMeetingDuringTimeByMeetingID(@Param("meeting_id")int meeting_id);

}
